﻿Public Class TodoDAL
    Inherits SQLiteDatabase
    Public ss As String = ""
    Public ctlName() As String
    Public ctlType() As Byte
    Public ctlContent() As String

    ''' <summary>
    ''' ctlContent(0) = UsrData.Number
    ''' ctlContent(1) = todoRec.private
    ''' ctlContent(2) = todoRec.task
    ''' ctlContent(3) = todoRec.Note
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DefineControlArrays()
        ss = ""
        ss = ss & "0 Owner 2 "
        ss = ss & "1 Private 1 "
        ss = ss & "2 Subject 10 "
        ss = ss & "3 Memo 12 "
    End Sub

    Public Sub GetControlArrays()
        ' redims  names(), type() and content() and assigns names and type constants to field arrays
        ' for all sections (called by  DefineControlArrays)
        Dim N As Long, i As Integer
        Dim v As String() = ss.Trim().Split(" ")
        N = UBound(v) \ 3
        ReDim ctlContent(N)
        ReDim ctlName(N)
        ReDim ctlType(N)

        For i = 0 To N
            ctlName(i) = v(i * 3 + 1)
            ctlType(i) = v(i * 3 + 2)
        Next i
    End Sub

    Public Sub New(ByVal inputFile As String)
        MyBase.New(inputFile)
        DefineControlArrays()
        GetControlArrays()
    End Sub
    ''' <summary>
    ''' p: 15
    ''' b 
    '''      byte:1 null
    '''      byte:2 10
    '''      byte:3 text length
    '''      byte:4 Content
    '''      byte:5 null
    '''      byte:6 null
    ''' c: thời gian tạo bản ghi (tính bằng hàm Now2Unix - "modDateTimeUTC")
    ''' d: bị xóa hay chưa (0|1)
    ''' e: thời gian sửa bản ghi (tính bằng hàm Now2Unix -"modDateTimeUTC")
    ''' r: trường dự trữ, không dùng
    ''' i,j,k,l,m: trường nguyên phục vụ sắp xếp
    ''' s,t,u,v,w: trường text thứ hai, được mã hóa 64 bit
    ''' x,y,z: cờ
    ''' g,h: trường nguyên để nhóm dữ liệu thành các mụcs
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function Insert(ByVal _todo As Todo)
        Dim data As New Dictionary(Of String, String)
        Dim sPln As String
        Try
            ctlContent(0) = ""
            ctlContent(1) = _todo.Pri
            ctlContent(2) = _todo.Task
            ctlContent(3) = _todo.Note

            sPln = ""
            For i = 0 To UBound(ctlContent)
                If ctlContent(i) <> "" Then sPln = sPln & Record_Put.PutData(ctlContent(i), ctlType(i), CByte(i))
            Next i
            MessageBox.Show(sPln)
            data.Add("e", "123")                '
            data.Add("b", sPln)                 'BLFIT(kID, 1, sPln)
            data.Add("f", "123")                'MD5(sPln)
            data.Add("s", "123")                'BLFIT(kID, 1, Chr(UsrData.Number) & IIf(todoRec.private, Chr(1), Chr(0)) & todoRec.task)
            data.Add("q", _todo.Priority)       'todoRec.pri
            data.Add("g", "123")                'If Not IsNumeric(todoRec.category) Then iG = 0 Else iG = todoRec.category
            data.Add("j", "123")                'D2I(todoRec.completedDate, itdThomas)
            data.Add("k", "123")                'D2I(todoRec.dueDate, itdJulia)
            data.Add("x", "123")                'IIf(todoRec.dueYN, 1, 0)
            data.Add("y", "123")                'IIf(todoRec.noteYN, 1, 0)
            data.Add("z", "123")                'IIf(todoRec.completedYN, 1, 0)
            data.Add("c", "123")                'c:=nNow
            data.Add("p", "15")                 'p:=CHAPTER
            Return MyBase.Insert(data)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
        
    End Function

    Public Overloads Function Update(ByVal _toto As Todo)
        Return True
    End Function

    Public Overloads Function GetDataTable() As DataTable
        Dim dt As New DataTable()
        Dim r As DataRow
        Dim c As DataColumn
        Dim sql As String = "SELECT * from tt where a=62"
        Dim rsc As String
        dt = MyBase.GetDataTable(sql)
        r = dt.Rows(0)
        c = dt.Columns("b")
        rsc = r(c).ToString
        ExtractFields(ctlContent, rsc)
        Return dt
    End Function

    Public Overloads Function GetData(ByVal id As Integer) As Object
        Dim _toDo As New Todo()
        Dim dt As New DataTable()
        Dim r As DataRow
        Dim c As DataColumn
        Dim sql As String = "SELECT * from tt where a= " & id
        Dim rsc As String
        dt = MyBase.GetDataTable(sql)
        r = dt.Rows(0)
        c = dt.Columns("b")
        rsc = r(c).ToString
        ExtractFields(ctlContent, rsc)

        _toDo.Id = id
        _toDo.Priority = ctlContent(1)
        _toDo.Task = ctlContent(2)
        _toDo.Note = ctlContent(3)
        Return _toDo
    End Function

    Public Overloads Function GetData() As Object
        Dim _toDo() As Todo
        Dim dt As New DataTable()
        Dim sql As String = "SELECT * from tt where p= 15"
        Dim rsc As String
        Dim i As Integer = 0
        dt = MyBase.GetDataTable(sql)

        ReDim _toDo(dt.Rows.Count - 1)

        For j = 0 To dt.Rows.Count - 1
            _toDo(j) = New Todo()
        Next j

        For Each r As DataRow In dt.Rows
            For Each c As DataColumn In dt.Columns
                Select Case c.ColumnName
                    Case "b"
                        rsc = r(c).ToString
                        ExtractFields(ctlContent, rsc)
                        _toDo(i).Pri = ctlContent(1)
                        _toDo(i).Task = ctlContent(2)
                        _toDo(i).Note = ctlContent(3)
                    Case "a"
                        _toDo(i).Id = r(c).ToString
                    Case Else
                End Select
            Next
            i += 1
        Next
        Return _toDo
    End Function
End Class
